﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Text;
using System.Collections;
using System.Collections.Generic;

/// <summary>
/// Summary description for DBInterface
/// </summary>
public class DBInterface
{
    private SqlConnection connection;
    int propertyID;

	public DBInterface(int propID)
	{
        string connectionString = ConfigurationManager.AppSettings.Get("strConn");
        connection = new SqlConnection(connectionString);
        connection.Open();
        propertyID = propID;
	}

    /*public ~DBInterface()
    {
        connection.Close();
    }*/

    public List<string> GetPropertyDetails()
    {
        List<string> propDetails = new List<string>();

        Property_Data pd = new Property_Data();
        DataSet ds = pd.GetDetails(propertyID);
        DataRow dr = ds.Tables[0].Rows[0];
        propDetails.Add(dr["Year_Built"].ToString()); //TBD - Add columns to database to retrieve all values
        
        return propDetails;
    }

    public string GetUserAppraisedValue()
    {
        string userAppraisedValue;
        userAppraisedValue = "0"; //TBD - have a call to stored procedures
        string commandText = "getUserAppraisalValue";
        SqlCommand command = new SqlCommand(commandText, connection);
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.AddWithValue("@property_id", propertyID); //TBD - send right property_id frm page
        SqlDataReader dataReader = command.ExecuteReader();
        if (dataReader.Read())
        {
            userAppraisedValue = dataReader["Appraisal_Value"].ToString();
        }
        dataReader.Close();
        return userAppraisedValue; 
    }

    public List<string> GetNearestAmenities()
    {
        List<string> distAmenities = new List<string>();
        string commandText = "getNearestAmenities";
        SqlCommand command = new SqlCommand(commandText, connection);
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.AddWithValue("@property_id", propertyID); //TBD - send right property_id frm page
        SqlDataReader dataReader = command.ExecuteReader();
        if (dataReader.Read())
        {
            distAmenities.Add(dataReader["dist_to_mall"].ToString());
            distAmenities.Add(dataReader["dist_to_city_center"].ToString());
            distAmenities.Add(dataReader["dist_to_park"].ToString());
            distAmenities.Add(dataReader["dist_to_hospital"].ToString());
            distAmenities.Add(dataReader["dist_to_school"].ToString());
            distAmenities.Add(dataReader["dist_to_library"].ToString());
        }
        dataReader.Close();
        return distAmenities;
    }
}
